package com.apestech.framework.db;

import com.apestech.framework.db.callback.ResultValueMapping;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.util.DateUtil;
import com.apestech.framework.util.StringUtil;

import java.io.StringReader;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;

public class PsmtUtil extends StringUtil {

    public static void setExecPram(PreparedStatement psmt, JSONArray param) throws SQLException {
        try {
            int len=param.size();
            for(int i=0; i<len; i++){
                JSONObject map=param.get(i);
                setPsmtData(map.getString("type"), i+1, map.get("value"), psmt);
            }
        } catch (Exception e) {
            throw new SQLException(e);
        }
    }

    public static void setExecPram(PreparedStatement psmt, SqlResultSet rs, ResultValueMapping resultValueMapping) throws SQLException {
        try {
            int len=rs.getMetaData().getColumnCount();
            for(int i=1; i<=len; i++){
                String field=rs.getMetaData().getColumnName(i);
                Object value=rs.getObject(i);
                if(resultValueMapping!=null){
                    value=resultValueMapping.getField(field, value, rs);
                }
                setPsmtData(rs.getMetaData().getColumnTypeName(i), i, value, psmt);
            }
        } catch (Exception e) {
            throw new SQLException(e);
        }
    }

    public static void setExecPram(ArrayList<PreparedStatement> psmts, SqlResultSet rs, ArrayList<String> fields) throws SQLException {
        setExecPram(psmts, rs, fields, null);
    }

    public static void setExecPram(ArrayList<PreparedStatement> psmts, SqlResultSet rs, ArrayList<String> fields, ResultValueMapping resultValueMapping) throws SQLException {
        try {
            ArrayList<ArrayList<Integer>> inx=getFieldsIndex(rs, fields);
            int len=rs.getMetaData().getColumnCount();
            int jlen=psmts.size();
            for(int i=1; i<=len; i++){
                String type=rs.getMetaData().getColumnTypeName(i);
                Object value=rs.getObject(i);
                String field=rs.getMetaData().getColumnName(i);
                if(resultValueMapping!=null){
                    value=resultValueMapping.getField(field, value, rs);
                }
                for(int j=0; j<jlen; j++){
                    Integer index=inx.get(j).indexOf(i)+1;
                    if(index>0) setPsmtData(type, index, value, psmts.get(j));
                }
            }
        } catch (Exception e) {
            throw new SQLException(e);
        }
    }

    private static ArrayList<ArrayList<Integer>> getFieldsIndex(SqlResultSet rs, ArrayList<String> fields) throws SQLException {
        ArrayList<ArrayList<String>> field=new ArrayList<ArrayList<String>>();
        ArrayList<ArrayList<Integer>> flist=new ArrayList<ArrayList<Integer>>();
        int flen=fields.size();
        for(int i=0; i<flen; i++){
            field.add(castToArrayStr(fields.get(i), ","));
            int len=field.get(i).size();
            ArrayList<Integer> inx=new ArrayList<Integer>();
            for(int j=0; j<len; j++){
                inx.add(-1);
            }
            flist.add(inx);
        }
        int len=rs.getMetaData().getColumnCount();
        for(int i=1; i<=len; i++){
            String k=rs.getMetaData().getColumnName(i);
            for(int j=0; j<flen; j++){
                Integer z=field.get(j).indexOf(k);
                if(z>=0) flist.get(j).set(z,i);
            }
        }
        return flist;
    }

    private static void setVarchar(int index, Object value, PreparedStatement psmt) throws SQLException {
        if(value==null){
            psmt.setNull(index, Types.VARCHAR);
        } else {
            psmt.setString(index, castToString(value));
        }
    }

    private static void setCLOB(int index, Object value, PreparedStatement psmt) throws SQLException {
        if(value==null){
            psmt.setNull(index, Types.CLOB);
        } else {
            String s=castToString(value);
            StringReader reader = new StringReader(s);
            psmt.setCharacterStream(index, reader, s.length());
        }
    }

    private static void setBig(int index, Object value, PreparedStatement psmt) throws SQLException {
        if(value==null){
            psmt.setNull(index, Types.BIGINT);
        } else {
            psmt.setBigDecimal(index, castToBigDecimal(value));
        }
    }

    private static void setNumber(int index, Object value, PreparedStatement psmt) throws Exception {
        if(value==null){
            psmt.setNull(index, Types.DOUBLE);
        } else {
            psmt.setDouble(index, castToDouble(value));
        }
    }

    private static void setDateTime(int index, Object value, PreparedStatement psmt) throws Exception {
        if(value==null){
            psmt.setNull(index, Types.TIMESTAMP);
        } else {
            String nclass=value.getClass().getName();
            if("oracle.sql.TIMESTAMP".equals(nclass)){
                psmt.setTimestamp(index, new Timestamp(((oracle.sql.TIMESTAMP) value).dateValue().getTime()));
            } else {
                psmt.setTimestamp(index, new Timestamp(castToDate(value).getTime()));
            }
        }
    }

    private static void setPsmtData(String type, int i, Object value, PreparedStatement psmt) throws Exception {
        if("null".equals(value)) value=null;
        if(type.indexOf("CHAR")>=0) setVarchar(i, value, psmt);
        else if("CLOB".equals(type)) setCLOB(i, value, psmt);
        else if("BIG".equals(type)) setBig(i, value, psmt);
        else if("NUMBER".equals(type) || "FLOAT".equals(type)) setNumber(i, value, psmt);
        else if("DATE".equals(type) || type.indexOf("TIMESTAMP")>=0) setDateTime(i, value, psmt);
    }

    public static void setProcPram(CallableStatement cstm, JSONArray param) throws SQLException{
        try{
            int len=param.size();
            for(int i=0; i<len; i++){
                JSONObject map=param.get(i);
                String type=map.getString("type");
                String direction=map.getString("direction");
                switch(type){
                    case "VARCHAR2":
                        if(direction.indexOf("in")>=0){
                            cstm.setString(i+1, map.getString("value"));
                        }
                        if(direction.indexOf("out")>=0){
                            cstm.registerOutParameter(i+1, java.sql.Types.CHAR);
                        }
                        break;
                    case "NUMBER":
                        if(direction.indexOf("in")>=0){
                            cstm.setBigDecimal(i+1, map.getBigDecimal("value"));
                        }
                        if(direction.indexOf("out")>=0){
                            cstm.registerOutParameter(i+1, java.sql.Types.NUMERIC);
                        }
                        break;
                    case "DATE":
                        if(direction.indexOf("in")>=0){
                            cstm.setTimestamp(i+1,  new Timestamp(map.getDate("value").getTime()));
                        }
                        if(direction.indexOf("out")>=0){
                            cstm.registerOutParameter(i+1, java.sql.Types.TIMESTAMP);
                        }
                        break;
                    case "CURSOR":
                        cstm.registerOutParameter(i+1, java.sql.Types.REF_CURSOR);
                        break;
                }
            }
        } catch (Exception e) {
            throw new SQLException(e);
        }
    }

    public static String getPrintProg(String progName, JSONArray param) throws Exception {
        try{
            String str="";
            String out="";
            String pout="";
            int len=param.size();
            for(int i=0; i<len; i++){
                JSONObject map=param.get(i);
                String type=map.getString("type");
                String direction=map.getString("direction");
                String key=map.getString("key");
                if(!"".equals(str)){
                    str=str+",";
                }
                switch(type){
                    case "VARCHAR2":
                        String str_val=map.getString("value");
                        if(direction.indexOf("in")>=0){
                            if(direction.indexOf("out")<0){
                                if(str_val==null){
                                    str=str+str_val;
                                } else {
                                    str=str+"'"+str_val+"'";
                                }

                            }
                        }
                        if(direction.indexOf("out")>=0){
                            out=out+"  "+key+" VARCHAR(1000)";
                            str=str+key;
                            if(str_val!=null){
                                out=out+":='"+str_val+"';\n";
                            } else {
                                out=out+";\n";
                            }
                            pout=pout+"  dbms_output.put_line('"+key+"：'||"+key+");\n";
                        }
                        break;
                    case "NUMBER":
                        BigDecimal big_val=map.getBigDecimal("value");
                        if(direction.indexOf("in")>=0){
                            if(direction.indexOf("out")<0){
                                str=str+big_val.toString();
                            }
                        }
                        if(direction.indexOf("out")>=0){
                            out=out+"  "+key+" NUMBER";
                            str=str+key;
                            if(big_val!=null){
                                out=out+":='"+big_val.toString()+"';\n";
                            } else {
                                out=out+";\n";
                            }
                            pout=pout+"  dbms_output.put_line('"+key+"：'||"+key+");\n";
                        }
                        break;
                    case "DATE":
                        long date_val=map.getDate("value").getTime();
                        if(direction.indexOf("in")>=0){
                            if(direction.indexOf("out")<0){
                                str=str+"TO_DATE('"+ DateUtil.dateTimeToStr(new Date(date_val))+"','YYYY-MM-DD HH24:MI:SS')";
                            }
                        }
                        if(direction.indexOf("out")>=0){
                            out=out+"  "+key+" TIMESTAMP(6)";
                            str=str+key;
                            if(map.getDate("value")!=null){
                                out=out+":=TO_DATE('"+ DateUtil.dateTimeToStr(new Date(date_val))+"','YYYY-MM-DD HH24:MI:SS');\n";
                            } else {
                                out=out+";\n";
                            }
                            pout=pout+"  dbms_output.put_line('"+key+"：'||"+key+");\n";
                        }
                        break;
                }
            }
            String result="";
            if(!"".equals(out)){
                result="DECLARE\n"+out;
            }
            result=result+"BEGIN\n  "+progName+"("+str+");\n";
            result=result+pout;
            result=result+"END;";
            return result;
        } catch (Exception e) {
            throw e;
        }
        /*
DECLARE
  p_1 INT;
  p_3 VARCHAR(10):='234';
BEGIN
  HP_TEST1(p_1,'123', p_3, '111');
  dbms_output.put_line('p_1：'||p_1);
  dbms_output.put_line('p_3：'||p_3);
END;
        */
    }

    private static void setVarchar(int index, Object value, CallableStatement psmt) throws SQLException {
        if(value==null){
            psmt.setNull(index, Types.VARCHAR);
        } else {
            psmt.setString(index, castToString(value));
        }
    }

    private static void setBig(int index, Object value, CallableStatement psmt) throws SQLException {
        if(value==null){
            psmt.setNull(index, Types.BIGINT);
        } else {
            psmt.setBigDecimal(index, castToBigDecimal(value));
        }
    }

    private static void setNumber(int index, Object value, CallableStatement psmt) throws Exception {
        if(value==null){
            psmt.setNull(index, Types.DOUBLE);
        } else {
            psmt.setDouble(index, castToDouble(value));
        }
    }

    private static void setDateTime(int index, Object value, CallableStatement psmt) throws Exception {
        if(value==null){
            psmt.setNull(index, Types.TIMESTAMP);
        } else {
            psmt.setTimestamp(index, new Timestamp(castToDate(value).getTime()));
        }
    }

    public static JSONObject getProgOut(CallableStatement cstm, JSONArray param) throws SQLException{
        JSONObject p=null;
        int len=param.size();
        if(len>0){
            p=new JSONObject();
        }
        for(int i=0; i<len; i++){
            JSONObject map=param.get(i);
            String type=map.getString("type");
            String direction=map.getString("direction");
            String key=map.getString("key");
            String ckey="";
            if(direction.indexOf("out")>=0){
                switch(type){
                    case "VARCHAR2":
                        p.put(key, cstm.getString(i+1));
                        break;
                    case "NUMBER":
                        p.put(key, cstm.getBigDecimal(i+1));
                        break;
                    case "DATE":
                        p.put(key, cstm.getTimestamp(i+1));
                        break;
                    case "CURSOR":
                        if(!"".equals(ckey)){
                            ckey=ckey+",";
                        }
                        p.put(key, cstm.getObject(i+1));
                        break;
                }
            }
            if(!"".equals(ckey)){
                p.put("cursor", ckey);
            }
        }
        return p;
    }

}
